package com.crayon2f.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by feifan.gou@gmail.com on 2025/8/6 11:30.
 */
public class TEST2025_08 {
    // MySQL 配置
    private static final String DB_URL = "jdbc:mysql://localhost:7002/basedata";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "CIM@mysql2025root";

    // 要查询的 MySQL 表和字段
    private static final String QUERY_DEVICE_SQL = "select id, del_flag, create_time from device_base_info where station_id = ? and device_id = ?";
    private static final String QUERY_POINT_SQL = "select id from point_base_info where device_id = ? and del_flag = 0";

    public static void main(String[] args) {
        String excelFilePath = "C:\\Users\\goufeifan\\Desktop\\Excel\\check_device.xlsx";  // 输入 Excel 文件路径
        String outputExcelPath = "C:\\Users\\goufeifan\\Desktop\\Excel\\check_device - short - 1.xlsx";  // 输出 Excel 文件路径
        try (
                // 1. 读取 Excel 文件
                FileInputStream fis = new FileInputStream(excelFilePath);
                Workbook workbook = new XSSFWorkbook(fis);
                // 2. 连接 MySQL
                Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)
        ) {
            Sheet sheet = workbook.getSheetAt(0);  // 读取第一个 Sheet
            // 3. 遍历 Excel 每一行（从第2行开始，假设第1行是表头）
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;

                // 4. 获取第1、2列的值（作为查询条件）
                Cell col1Cell = row.getCell(0);  // 第1列（索引0）
                Cell col2Cell = row.getCell(1);  // 第2列（索引1）

                String col1Value = getCellValueAsString(col1Cell);
                String col2Value = getCellValueAsString(col2Cell);
                String col3Value;
                if (col2Value.contains("_L_") || col2Value.length() >= 19) {
                    col3Value = "传参格式有误";
                } else {
                    col3Value = queryMySQL(conn, col1Value, col2Value);
                }
                System.out.printf("%d: %s %s %s\n", rowNum, col1Value, col2Value, col3Value);

                // 6. 将查询结果写入第3列（索引2）
                Cell col3Cell = row.getCell(2);
                if (col3Cell == null) {
                    col3Cell = row.createCell(2);
                }
                col3Cell.setCellValue(col3Value);
            }

            // 7. 保存修改后的 Excel
            try (FileOutputStream fos = new FileOutputStream(outputExcelPath)) {
                workbook.write(fos);
            }

            System.out.println("处理完成，结果已保存到：" + outputExcelPath);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 从 Excel 单元格获取字符串值
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf((long) cell.getNumericCellValue());  // 假设是整数
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    // 查询 MySQL 并返回目标字段
    private static String queryMySQL(Connection conn, String col1, String col2) {
        try (PreparedStatement pstmt = conn.prepareStatement(QUERY_DEVICE_SQL)) {
            pstmt.setString(1, col1);
            pstmt.setString(2, col2);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    if (rs.getBoolean("del_flag")) {
                        return "逻辑删除";
                    } else {
                        long id = rs.getLong("id");
                        try (PreparedStatement psPoint = conn.prepareStatement(QUERY_POINT_SQL)) {
                            psPoint.setLong(1, id);
                            try (ResultSet rsPoint = psPoint.executeQuery()) {
                                if (!rsPoint.next()) {
                                    return "设备存在，但设备下没测点";
                                } else {
                                    return String.format("存在，创建时间为：%s", rs.getDate("create_time"));
                                }
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "无数据";  // 如果没查到，返回默认值
    }
}
